# -*- coding: utf-8 -*-
import time
import math
import numpy as np
# from riskparty import calcu_w
import pymysql ##mysql

# categoryCode:categoryName
# 001:固收 002:债券 003:股票 004:黄金 005:海外
cat_code_name = {'001': '固收', '002': '债券', '003': '股票', '004': '黄金', '005': '海外'}

INVESTABLE_ASSETS_MATRIX = [
    ['001', '001', '001', '001', '001'],
    ['001', '002', '002', '003', '003'],
    ['002', '002', '003', '002', '002'],
    ['002', '003', '003', '004', '004'],
    ['002', '003', '004', '004', '005'],
    ['002', '003', '005', '005', '005']
]

ASSETS_ORDER = ['001', '002', '003', '004', '005']


class BaseSQLDB():
    def __init__(self, db_param = {#我们资管维护的最新数据库
        "host": "10.13.153.253",
        "port": 3306,
        "user": "jdjr",
        "passwd": "jdjr",
        "db": "m_sfmra",
        "charset": "utf8"
    }):
        
#        ## Mysql 使用
         self.conn = pymysql.connect(host=db_param['host'], port=db_param['port'], user=db_param['user'], passwd=db_param['passwd'], db=db_param['db'], charset=db_param['charset'])
         self.cursor = self.conn.cursor()

    
    def insert_normal(self,sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('insert_normal err:',e)
    
    #insert one row
    def insert_single(self,sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('insert single error: ',e)
    
    # insert many rows，items is the data to insert
    def insert_many(self,sql,items):
        try:
            self.cursor.executemany(sql,items)
            self.conn.commit()
        except Exception as e:
            print('insert many error: ',e)
            
    ##依据sql查询        
    def select(self,sel_str):
        try:
            self.cursor.execute(sel_str)
            data = self.cursor.fetchall()
            return data
        except Exception as e:
            print('select err: ',e)
            return None
        

# def get_categoryLimits(categoryLimitCoefficients,liquity,monthIncome,Pmt,currentAsset,asset_investable,insuranceAmt,fixAsset):
def get_categoryLimits(categoryLimitCoefficients, monthSpend,liquity, monthIncome, Pmt, currentAsset, asset_investable, fixAsset):
    #Max{Max[Min（月支出*2+流动性变动项 – 月收入 +白条/金条当月应还总额，用户持仓总金额+用户投资金额），定期持仓总金额]+a，b}
    # num = max(min(monthSpend * 2 + liquity - monthIncome + Pmt, currentAsset + asset_investable - insuranceAmt),fixAsset)
    num = max(min(monthSpend * 2 + liquity - monthIncome + Pmt, currentAsset + asset_investable),fixAsset)
    categoryLimits= [ [num+x[0],x[1]] for x in categoryLimitCoefficients]
    return categoryLimits

##获得可投资产类型  有修改
##assetsAmount:可投资产金额
##monthSpend：月度支出
##baitiaoRemain：白条余额
##返回资产向量（可投为1，不可投为0）,j为风险承受等级
def getInvestableAssets(lambda_i, assetsAmount, categoryLimits,business_info):
    i = 0;
    j = 0;
    # 修改投资档位
    if lambda_i >= 6 and lambda_i <= 7:
        j = 0
    elif lambda_i >= 5 and lambda_i < 6:
        j = 1
    elif lambda_i >= 4 and lambda_i < 5:
        j = 2
    elif lambda_i >= 3 and lambda_i < 4:
        j = 3
    elif lambda_i >= 2 and lambda_i < 3:
        j = 4

    if assetsAmount <= max(categoryLimits[0]):
        i = 0
    elif assetsAmount < max(categoryLimits[1]):
        i = 1
    elif assetsAmount < max(categoryLimits[2]):
        i = 2
    elif assetsAmount < max(categoryLimits[3]):
        i = 3
    elif assetsAmount < max(categoryLimits[4]):
        i = 4
    elif assetsAmount >= max(categoryLimits[4]):
        i = 5

    investable_assets = []

    last_assets = INVESTABLE_ASSETS_MATRIX[i][j]

    add_val = 1
    for ind in range(0, len(ASSETS_ORDER)):
        if last_assets == ASSETS_ORDER[ind]:

            investable_assets.append(add_val)
            add_val = 0
        else:
            investable_assets.append(add_val)

    if i == 1 and j >= 3:
        investable_assets = [1, 0, 1, 0, 0]
    if i == 2 and j >= 3:
        investable_assets = [1, 1, 1, 0, 0]

    categoryCodes = [x['categoryCode'][:3] for x in business_info]
    investable_assets_temp = [investable_assets[int(x[-1])-1] for x in categoryCodes]
    return investable_assets_temp, j

#新加函数 zmj  查看是否调用。
def get_maxBoundCoefficients(business_info,upper_limits):#根据入参得到maxBoundCoefficients资产比例上限参数
    maxBoundCoefficients = [] 
    #规则：根据保守型用户和激进型用户在资产i上的上限计算资产比例上限时各资产需要的参数
    for index, each_cat in enumerate(business_info):
        if each_cat['categoryCode'] == '003':#该资产属于A股
            coefficient_1 = (upper_limits[index][0]-upper_limits[index][1]+0.1)/math.log(3.5)
            coefficient_2 = upper_limits[index][1]-math.log(2)*coefficient_1

        else:
            coefficient_1 = (upper_limits[index][0]-upper_limits[index][1])/math.log(3.5)
            coefficient_2 = upper_limits[index][1] - math.log(2) * coefficient_1
        maxBoundCoefficients.append([coefficient_1, coefficient_2])
    return maxBoundCoefficients

#新加函数
def get_minBoundCoefficients(business_info,least_invest_amount):#根据入参得到minBoundCoefficients资产比例下限参数
    minBoundCoefficients = []
    #规则：按照文档得到计算资产比例下限时各资产需要的参数
    for index, each_cat in enumerate(business_info):
        if each_cat['categoryCode'] in  ['001','002']:#该资产属于固收或者债券
            minBoundCoefficients.append([least_invest_amount[index],1, 2])
        else:
            minBoundCoefficients.append([least_invest_amount[index], -1, 7])
    return minBoundCoefficients





##获得可投资产下限 函数改动
##lambda_i 风险厌恶系数
##asset_investable:用户投资金额
##currentAsset：用户持仓金额
## monthSpend 月总支出
## monthIncome 月总收入
##Pmt：白条/金条当月应还款金额
## mortagageAmt 房贷金额
##parents 是否父母赡养
## children 是否有子女
## fixAsset 定期持仓金额
## insuranceAmt 保险账户建议金额
##返回资产向量（可投为1，不可投为0）
# def getLowWeight(lambda_i, asset_investable, currentAsset, monthSpend,monthIncome,Pmt,liquity,fixAsset,insuranceAmt,business_info, minBoundCoefficients):
def getLowWeight(lambda_i, asset_investable, currentAsset, monthSpend, monthIncome, Pmt, liquity, fixAsset,
                     business_info, minBoundCoefficients):
    lowWeight = []
    for index, each_cat in enumerate(business_info):
        if each_cat['categoryCode'] == '001':#资产是固收
            # a1=float((monthSpend - baitiaoRemain - max(currentAsset, assetsAmount) + assetsAmount)/assetsAmount)
            # a2=float(minBoundCoefficients[index][0]) / assetsAmount * ( 1 + minBoundCoefficients[index][1] * math.log( lambda_i / float(minBoundCoefficients[index][2])))
            # lowWeight.append(max(a1,a2))

            #Min（月支出*2+流动性变动项 – 月收入 +白条/金条当月应还总额，用户持仓总金额+用户投资金额-保险账户建议金额）
            # a1 = min(monthSpend*2+liquity-monthIncome+Pmt,currentAsset+asset_investable-insuranceAmt)
            a1 = min(monthSpend * 2 + liquity - monthIncome + Pmt, currentAsset + asset_investable)
            #Max[a1，定期持仓总金额]/（用户投资资产+持仓总金额）
            a2 = max(a1,fixAsset)/(asset_investable+currentAsset)
            #least_invest_amount/（用户投资资产+持仓总金额）* (1+ln(λ/2))
            a3 = minBoundCoefficients[index][0] /(asset_investable+currentAsset)*(1 + minBoundCoefficients[index][1] * math.log(lambda_i / float(minBoundCoefficients[index][2])))
            lowWeight.append(max(a2,a3))
        else:
            # lowWeight.append(float(minBoundCoefficients[index][0]) / asset_investable * (1 + minBoundCoefficients[index][1] * math.log(lambda_i / float(minBoundCoefficients[index][2]))))
            lowWeight.append(float(minBoundCoefficients[index][0]) / (asset_investable+currentAsset) * (1 + minBoundCoefficients[index][1] * math.log(lambda_i / float(minBoundCoefficients[index][2]))))

    return lowWeight





##获得可投资产上限
##assetsAmount:可投资产金额
##monthSpend：月度支出
##baitiaoRemain：白条余额
##返回资产向量（可投为1，不可投为0）
def getHighWeight(lowWeight, lambda_i, maxBoundCoefficients):
    highWeight = []
    for index, low_value in enumerate(lowWeight):
        highWeight.append(
            max(low_value, maxBoundCoefficients[index][0] * math.log(lambda_i) + maxBoundCoefficients[index][1]))
#     print("highWeight",highWeight)
    return highWeight


##资产权重上下限调整
##investable_assets:可投资
##lowWeight：资产下限
##highWeight：资产上限
def checkAndAdjustWeight(investable_assets, lowWeight, highWeight, business_info, lambda_i=None):
    low_sum = 0
    high_sum = 0
    asset_index_list = [int(x['categoryCode'][:3]) for x in business_info]
    for i in range(len(business_info)):
        lowWeight[i] = lowWeight[i] * investable_assets[i]
        highWeight[i] = highWeight[i] * investable_assets[i]
        low_sum += lowWeight[i]
        high_sum += highWeight[i]

    # 检查下限不能大于1
    if low_sum > 1:
        # 需要调整
        if lambda_i >= 4:
            order = "0,1,2,3,4"
        elif lambda_i < 4:
            order = "0,2,1,3,4"

        arr = order.split(',')
        sum = 0
        for m in range(0, len(arr)):
            if sum + lowWeight[int(arr[m])] <= 1:
                pass
            else:
                # 下限之和大于1的资产，上下界约束都设为0--不投该资产，继续尝试下一项
                lowWeight[int(arr[m])] = 0
                highWeight[int(arr[m])] = 0
        # 若只剩下固收，则将其上限提高到1
        if len([x for x in lowWeight if x != 0]) <= 1:
            highWeight[0] = 1

    ##检查上限不能小于1
    if high_sum < 1:
        ##需要调整
        for i in range(0, len(highWeight)):
            highWeight[i] = highWeight[i] * 1 / high_sum

    return lowWeight, highWeight


# 计算资产收益的Σ
def cal_simga(cols_change):
    for i in range(len(cols_change)):
        # --环比
        temp_list = [float(x) for x in cols_change[i]]
        temp_arr = np.array(temp_list[1:]) - np.array(temp_list[:-1])
        temp_rate = temp_arr / np.array(temp_list[:-1])
        cols_change[i] = temp_rate

        # 对数收益率
        # temp_list = [float(x) for x in cols_change[i]]
        # temp_arr = np.array(temp_list[1:]) / np.array(temp_list[:-1])
        # temp_rate = [math.log(val) for val in temp_arr]
        # cols_change[i] = temp_rate

        # # 同比
        # base_value = [cols_change[i][0]]
        # temp_list = []
        # for j in range(1,len(cols_change[i])):
        #     temp_list.append(float(cols_change[i][j]-cols_change[i][0])/cols_change[i][0])
        # cols_change[i] = temp_list
    # print len(cols_change[0])
    return np.cov(np.array(cols_change)).tolist()


def update_var_local(business_info, recommendDate):
    # recommendDate = time.mktime(time.strptime(recommendDate,'%Y-%m-%d'))
    cols_change = []
    # 确定资产类别
    index_code = []
    for idx in business_info:
        index_code.append(''+idx['indexCode'].replace('.','_')+'')

    db = BaseSQLDB() #连接test数据库
    # start_time=time.mktime(time.strptime("2013-01-01",'%Y-%m-%d'))
    start_time ='2013-01-01'
    print('select %s from bl_index_data WHERE index_date >= "%s" AND index_date <= "%s"' % (
    ','.join(index_code), start_time, recommendDate))

    cols = db.select("select %s from bl_index_data WHERE index_date >= '%s' AND index_date <= '%s'" % (
    ','.join(index_code), start_time, recommendDate,))
    
    for i in range(0, len(cols[0])):
        cols_change.append([x[i] for x in cols])  # (x[i+1]-min(x[i+1]))/(max(x[i+1])-min(x[i+1]))
    # 所选资产的收益的协方差矩阵
    # var_cache 保存最新的sigma,特殊的日期需求不保存
    sigema = cal_simga(cols_change)
    # 均衡配置的资产配置比例 风险平价模型计算
    # x_t = [0.20, 0.20, 0.2, 0.2, 0.2]#有修改
    # x_t =np.ones(len(json_str['business_info'])) * 1.0 / len(json_str['business_info'])

    business_len = len(business_info)
    x_t = np.ones(business_len) * 1.0 / business_len
    omiga_mkt = calcu_w(sigema, x_t)
    return {'sigema': sigema, 'omiga_mkt': omiga_mkt}


# 获取五大类的收益率协方差∑和Ω_mkt

##获取系统参数


if __name__ == '__main__':
    pass
